package com.bst.etl.service.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.bst.base.domain.BaseDb;
import com.bst.base.domain.BaseDbfldComb;
import com.bst.base.service.IBaseDbService;
import com.bst.base.service.IBaseDbfldCombService;
import com.bst.common.exception.base.BaseException;
import com.bst.common.vo.MetaDataVO;
import com.bst.common.vo.TableDDLColumnVO;
import com.bst.etl.service.ITableMetaQueryService;
import com.bst.etl.vo.OdsTableCreSqlVO;
import com.bst.etl.vo.TableFieldVO;
import com.bst.system.framework.datasource.DynamicDataSource;
import com.bst.system.framework.utils.DBUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Triple;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
public class TableMetaQueryServiceImpl implements ITableMetaQueryService {

    @Autowired
    IBaseDbfldCombService baseDbfldCombService;
    @Autowired
    IBaseDbService baseDbService;

    @Override
    public List<TableFieldVO> findAllFldsByTableName(String tableName, String idDb) {
        return findAllFldsBySql("select * from "+tableName+" where 1=2",idDb);
    }

    @Override
    public List<TableFieldVO> findAllFldsBySql(String sql,String idDb) {
        if(idDb==null) {
            idDb="dw";
        }
        Triple<List<String>, List<Integer>, List<String>> columnMetaDataTo =
                DBUtil.getColumnMetaDataBySql(DynamicDataSource.getDataSourceById(idDb+""), sql);
        List<String> flds = new ArrayList<>();
        List<Integer> colTypes = columnMetaDataTo.getMiddle();
        List<String> left = columnMetaDataTo.getLeft();
        List<TableFieldVO> fieldVOS = new ArrayList<>();
        TableFieldVO fieldVO;
        for (int i = 0; i < colTypes.size(); i++) {
            fieldVO = new TableFieldVO();
            fieldVO.setName(left.get(i).toUpperCase());
            fieldVO.setType(colTypes.get(i));
            fieldVOS.add(fieldVO);
        }
        return fieldVOS;
    }

    @Override
    public String queryTableCreSql(String tableName,Integer idDb) throws SQLException {
        return DBUtil.queryCreatTableSql(tableName,idDb);
    }

    @Override
    public String queryOdsTableCreSql(OdsTableCreSqlVO paramVO) throws BaseException {
        BaseDb db = baseDbService.selectBaseDbById(paramVO.getIdDb());
        paramVO.setDb(db);
        DruidDataSource dataSource = DynamicDataSource.getDataSourceById(paramVO.getIdDb()+"");
        if (dataSource == null) {
            throw new BaseException(String.format("数据源未配置"));
        }
        DruidDataSource tarDataSource = DynamicDataSource.getDataSourceById("dw");
        if (tarDataSource == null) {
            throw new BaseException(String.format("数据仓库未配置数据源"));
        }
        String tarEuTp = tarDataSource.getDbType();
        // 列类型字典
        BaseDbfldComb BaseDbfldCombModel = new BaseDbfldComb();
        BaseDbfldCombModel.setSdDbtp(tarEuTp);
        List<BaseDbfldComb> baseDbfldCombs = baseDbfldCombService.selectBaseDbfldCombList(BaseDbfldCombModel);
        Map<Integer, BaseDbfldComb> columnTypes = new LinkedHashMap<>();
        for (BaseDbfldComb comb : baseDbfldCombs) {
            if(columnTypes.get(comb.getEuJavatp())==null) {
                columnTypes.put(comb.getEuJavatp(),comb);
            }
        }

        Map<String, MetaDataVO> queryColumnMetaData = null;
        try {
            List<TableDDLColumnVO> columns = new ArrayList<>();
            if(StringUtils.isNotEmpty(paramVO.getQrySql())) {
                queryColumnMetaData = DBUtil.getColumnMetaDataToMetaDataVO(dataSource, paramVO.getQrySql());
            } else {
                queryColumnMetaData = DBUtil.getTableColumnMetaDataToMetaDataVO(dataSource, paramVO.getTableName());
            }
            List<String> columnDDLs = new ArrayList<>();
            StringBuilder commentSql = new StringBuilder("\n");
            String tarTableName = ("ods_"+db.getCd()+"_"+paramVO.getTableName()).toLowerCase();
            for (String queryColumnName : queryColumnMetaData.keySet()) {
                MetaDataVO queryColumn = queryColumnMetaData.get(queryColumnName);
                String columnDDL = generateTableColumnDDL(columnTypes,queryColumn,tarEuTp);
                if(StringUtils.isEmpty(columnDDL)) {
                    continue;
                }
                String comment = queryColumn.getColumnComment();
                if(StringUtils.isNotEmpty(comment)) {
                    if("mysql".equals(tarEuTp) || "doris".equals(tarEuTp)) {
                        commentSql.append("ALTER TABLE "+tarTableName+" MODIFY COLUMN "+columnDDL+" COMMENT '"+comment+"';\n");
                    } else if("sqlserver".equals(tarEuTp)) {
                        commentSql.append(" exec sp_addextendedproperty 'MS_Description', N'"+comment+"','SCHEMA', 'dbo','TABLE', '"+tarTableName+"','COLUMN','"+queryColumn.getColumnName()+"' ;\n");
                    }

                }
                columnDDLs.add(columnDDL);
            }
            StringBuilder sb = new StringBuilder();
            sb.append("CREATE TABLE ").append(tarTableName).append("(\n").append(StringUtils.join(columnDDLs, " ,\n")).append(" \n) ");
            if("doris".equals(tarEuTp)) {
                sb.append("UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS 1\n" +
                        "PROPERTIES (\n" +
                        "  \"replication_num\" = \"1\",\n" +
                        "  \"enable_unique_key_merge_on_write\" = \"true\"\n" +
                        ");");
            }
            return sb.toString()+commentSql.toString();
        } catch (Exception e) {
            throw new BaseException(e.getMessage());
        }
    }



    private String generateTableColumnDDL(Map<Integer, BaseDbfldComb> columnTypes,MetaDataVO metaDataVO,String euTp) {
        BaseDbfldComb BaseDbfldComb = columnTypes.get(metaDataVO.getColumnType());
        if (BaseDbfldComb == null) {
            return null;
        }
        String commont = metaDataVO.getColumnComment();
        Integer scale = metaDataVO.getColumnScale();
        Integer precision = metaDataVO.getColumnPrecision();
        //precision = "doris".equals(euTp)?precision*2:precision;
        StringBuilder createSql = new StringBuilder();
        StringBuilder commentSql = new StringBuilder("");
        String euFldtp = BaseDbfldComb.getEuDbfldtp();
        createSql.append(metaDataVO.getColumnName()).append(" ").append(euFldtp);
        if (precision != null && precision > 0 && scale != null && scale > 0 && BaseDbfldComb.getFgPrec() == 1) {
            createSql.append("(").append(precision).append(",").append(scale).append(")");
        } else if (precision != null && precision > 0 && (scale == null || scale <= 0) && BaseDbfldComb.getFgPrec() == 1) {
            createSql.append("(").append(precision).append(")");
        }
        return createSql.toString()+commentSql.toString();
    }
}
